package com.lee;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.lee.entry.CreateSql;
import com.lee.entry.QuerySql;
import com.lee.entry.TableInfo;
import com.lee.utils.*;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * @author Boomlee
 * @description TODO
 * @date 2022/1/29 15:50
 */


public class Main {


    public static void main(String[] args) throws Exception {

        Connection conn = HiveJdbcUtil.getConnection();

        Statement stat = conn.createStatement();

        // 获取所有数据库
        HashMap<String, List<String>> hiveAllTables = HiveJdbcUtil.getAllTableList();

        System.out.println("统计任务总共   --->  " + hiveAllTables.size());
        int size = hiveAllTables.size();

        // 所有信息的对象集合

        ArrayList<TableInfo> tableInfoList = new ArrayList<>();

        ArrayList<QuerySql> querySqls = new ArrayList<>();

        // 建表语句
        ArrayList<CreateSql> createSqlList = new ArrayList<>();

        // 精简建表语句
        ArrayList<CreateSql> simpleCreateSqlList = new ArrayList<>();

        for (Map.Entry<String, List<String>> entry : hiveAllTables.entrySet()) {

            System.out.println(new Date() + "  正在分析  ----> " + entry.getKey() + "    还剩 " + size--);

            List<String> tableList = entry.getValue();

            for (String s : tableList) {

                try {
                    if (PropertiesUtil.getValue("hive.analyse").contains("true")) {
                        stat.execute("ANALYZE TABLE " + entry.getKey() + "." + s + " COMPUTE STATISTICS");
                    }
                } catch (Exception e) {
                    System.out.println(e);
                }
                ResultSet resultSet = stat.executeQuery("desc formatted " + entry.getKey() + "." + s);
                TableInfo tableInfo = new TableInfo();


                tableInfo.setDatabaseName(entry.getKey());
                tableInfo.setTableName(s);
                // sheet2 （全字段查询SQL）生成
                QuerySql querySql = new QuerySql();
                querySql.setDatabaseName(entry.getKey());
                querySql.setTableName(s);
                querySql.setOverWriteSql("insert overwrite table " + entry.getKey() + "." + s + " select * from " + entry.getKey() + "." + s);

                // 库表名  database.tableName
                tableInfo.setFullName(entry.getKey() + "." + s);

                CreateSql createSql = new CreateSql();
                try {

                    ResultSet resultSet1 = conn.createStatement().executeQuery("show create table " + entry.getKey() + "." + s);

                    StringBuilder sb = new StringBuilder();
                    while (resultSet1.next()) {
                        String createStmt = resultSet1.getString("createtab_stmt");
                        sb.append(createStmt + "\n");
                    }
                    createSql.setCreateSql(sb.toString());
                    createSql.setDatabaseName(entry.getKey());
                    createSql.setTableName(s);
                } catch (Exception e) {
                    System.out.println(s + "  ---->  " + e);
                }

                // 生成simple 建表语句
                ResultSet resultSet1 = conn.createStatement().executeQuery("desc " + entry.getKey() + "." + s);
                StringBuilder sb = new StringBuilder();
                sb.append("create table " + entry.getKey() + "." + s + "( \n");
                while (resultSet1.next()) {
                    sb.append("`" + resultSet1.getString("col_name") + "`").append(" ").append(resultSet1.getString("data_type"));
                    sb.append(",\n");
                }
                // 移除最后一个逗号和换行符
                sb.setLength(sb.length() - 2);
                sb.append("\n);");
                createSql.setSimpleCreateSql(sb.toString());


                //生成查询SQL
                querySql.setSelectSql(SqlUtils.getSelectSql(entry.getKey() + "." + s, conn));
                querySql.setColumnCount(SqlUtils.getColumnoCount(entry.getKey() + "." + s, conn));

                while (resultSet.next()) {
                    if (resultSet.getString("data_type") != null) {
                        if (resultSet.getString("data_type").contains("totalSize")) {
                            String totalSize = resultSet.getString(3).replace(" ", "");
                            tableInfo.setSize(ByteChange.getSize(totalSize));
                            tableInfo.setTotalSize(new BigDecimal(totalSize));
                        }
                        if (resultSet.getString("data_type").contains("numRows")) {
                            String rowNum = resultSet.getString(3).replace(" ", "");
                            tableInfo.setNumRows(new BigDecimal(rowNum));
                        }
                        if (resultSet.getString("col_name").contains("Table Type")) {
                            String tableType = resultSet.getString(2).replace(" ", "");
                            tableInfo.setTableType(tableType);
                        }
                        //文件类型 parquet orc text
                        if (resultSet.getString("col_name").contains("InputFormat")) {
                            String fileType = resultSet.getString(2).replace(" ", "");
                            if (fileType.contains("Orc")) {
                                tableInfo.setFileType("Orc");
                            } else if (fileType.contains("Parquet")) {
                                tableInfo.setFileType("Parquet");
                            } else if (fileType.contains("Text")) {
                                tableInfo.setFileType("Text");
                            }

                        }

                        //Location
                        if (resultSet.getString("col_name").contains("Location")) {
                            String location = resultSet.getString(2).replace(" ", "");
                            tableInfo.setLocation(location);
                        }


                    }
                }
                querySqls.add(querySql);
                createSqlList.add(createSql);
                tableInfoList.add(tableInfo);
            }
        }


        String filename = DateUtils.getNowDate();
        try (ExcelWriter excelWriter = EasyExcel.write(filename + ".xlsx").build()) {

            WriteSheet writeSheet = EasyExcel.writerSheet(1, "基本信息").head(TableInfo.class).build();
            excelWriter.write(tableInfoList, writeSheet);

            WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "查询SQL").head(QuerySql.class).build();
            excelWriter.write(querySqls, writeSheet2);

            WriteSheet writeSheet3 = EasyExcel.writerSheet(3, "建表SQL").head(CreateSql.class).build();
            excelWriter.write(createSqlList, writeSheet3);

        } catch (Exception e) {
            System.out.println(e);
        }

        System.out.println("分析完成");
    }
}
